/**
 * <b>项目名称：</b>网络应用框架<br/>
 * <b>包    名：</b>com.hhwy.framework.configure.service<br/>
 * <b>文 件 名：</b>SystemConfigureFromDB.java<br/>
 * <b>版本信息：</b><br/>
 * <b>日    期：</b>2016年8月12日-上午10:01:16<br/>
 * <b>Copyright (c)</b> 2016恒华伟业科技股份有限公司-版权所有<br/>
 * 
 */
package com.hhwy.framework.configure.service;

import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collections;
import java.util.List;
import java.util.Map;
import java.util.Properties;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.core.io.ClassPathResource;
import org.springframework.core.io.Resource;
import org.springframework.core.io.support.PropertiesLoaderUtils;

import com.hhwy.framework.util.CaseInsensitiveMap;
import com.hhwy.framework.util.PlatformTools;

 /**
 * <b>类 名 称：</b>SystemConfigureFromDB<br/>
 * <b>类 描 述：</b><br/>
 * <b>创 建 人：</b>jlx<br/>
 * <b>修 改 人：</b>jlx<br/>
 * <b>修改时间：</b>2016年8月12日 上午10:01:16<br/>
 * <b>修改备注：</b><br/>
 * @version 1.0.0<br/>
 */
public class SystemConfigureFromDB {

	private static Logger log = LoggerFactory.getLogger(SystemConfigureFromDB.class);
	
	public SystemConfigureFromDB(String driverClass,String url,String uname,String pass){
		this.driverClass=driverClass;
		this.url=url;
		this.uname=uname;
		this.password=pass;
	}
	
	private String url;
	
	private String driverClass="com.mysql.jdbc.Driver";
	
	private String uname;
	
	private String password;
	
	protected Connection conn;
	
	protected PreparedStatement psmt;
	
	
	public Map<String, String> loadConfig() throws SQLException{
		
		try {
			
			if (!this.existsTable()) {
				
				this.createTable();
				
			}
			
			this.open();
			
			Map<String, String> map=new CaseInsensitiveMap<String>();
			
			this.psmt=this.conn.prepareStatement(" SELECT `key`,`value` FROM system_config ");
			
			ResultSet rset=this.psmt.executeQuery();
			
			while (rset.next()) {
				
				String key=rset.getString("key");
				
				String value=rset.getString("value");
				
				map.put(key, value);
			}
			
			return map;
			
			
		} catch (Exception e) {
			e.printStackTrace();
			log.error("从数据库获取配置信息失败!!!");
		}finally{
			
			this.close();
		}
		
		throw new RuntimeException("从数据库获取配置信息为null");
	}
	
	private void createTable()throws SQLException{
		
		
		StringBuilder sb=new StringBuilder();
		
		sb.append(" CREATE TABLE `system_config` ( ");
		sb.append("  `id` varchar(32) NOT NULL , ");
		sb.append("  `name` varchar(100) NOT NULL, ");
		sb.append("  `key` varchar(100) NOT NULL, ");
		sb.append("  `value` varchar(255) NOT NULL, ");
		sb.append("  PRIMARY KEY (`id`), ");
		sb.append("  UNIQUE KEY `NewIndex1` (`key`) ");
		sb.append(" ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ");
		
		String insert=initialSql();

		try {
			
			this.open();
			
			this.psmt=this.conn.prepareStatement(sb.toString());
			
			int result=this.psmt.executeUpdate();
		
			log.info("配置表不存在,已经创建并初始化");
			
			if(insert!=null){
				
				this.psmt=this.conn.prepareStatement(insert);
				
				result=this.psmt.executeUpdate();
				
				log.info("配置表不存在,已经添加默认数据");
				
				if (result<=0) {
					throw new RuntimeException("插入初始数据失败!!!");
				}
				
			}
			
		} catch (Exception e) {
			e.printStackTrace();
			log.error("初始化配置表失败!!!");
		}finally{
			this.close();
		}
	}
	
	private boolean existsTable()throws SQLException{
		
		String sql="SELECT COUNT(*) FROM information_schema.tables WHERE table_name = 'system_config' and table_schema=(select database())";
		

		try {
			
			this.open();
			
			this.psmt=this.conn.prepareStatement(sql);
			
			ResultSet rset=this.psmt.executeQuery();
			
			if (rset.next()) {
				
				long count=rset.getLong(1);
				
				if (count>0) {
					return true;
				}
			}
			
			
		} catch (Exception e) {
			e.printStackTrace();
			log.error("从数据库获取配置信息失败!!!");
		}finally{
			this.close();
		}
		
		return false;
	}
	

	
	private Connection open() throws ClassNotFoundException, SQLException{
		
		Class.forName(driverClass);
		
		conn=DriverManager.getConnection(this.url, this.uname, this.password);
		
		return conn;
	}
	
	private void close() throws SQLException{
		
		if (psmt!=null) {
			psmt.close();
		}
		
		if (conn!=null) {
			conn.close();
		}
	}
	
	private String initialSql(){
		
		StringBuilder insert=new StringBuilder();
		
			Resource resource = new ClassPathResource("initial.properties");
		
			try {
				Properties props = PropertiesLoaderUtils.loadProperties(resource);
				
				if (props!=null&&props.size()>0) {
					
					List<String> keys=new ArrayList<>();
					
					insert.append(" INSERT INTO system_config(`id`,`name`,`key`,`value`) VALUES ");
					
					int i=0;
					
					for (Object string : props.keySet()) {
						keys.add(string+"");
					}
					
					Collections.sort(keys,String.CASE_INSENSITIVE_ORDER);
					
					for (Object key : keys) {
						
						String value=props.getProperty(key+"");//'
						
						if(value!=null){
							value=value.replace("'","''");
						}else{
							value="";
						}
						
						if (key!=null) {
							key=key.toString().toLowerCase();
						}
						
						insert.append(" ('"+PlatformTools.getID()+"','','"+key+"','"+value+"') ");
						
						if(i<props.keySet().size()-1){
							insert.append(", ");
						}
						
						i++;
					}
					
					return insert.toString();
				}
				
			} catch (IOException e) {
				e.printStackTrace();
			}
		
		return null;
	}
	
	
}
